/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Modelo.DAL;

import Modelo.Area;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import org.json.JSONObject;
import java.util.List;
import java.util.ArrayList;
import org.json.JSONArray;
/**
 *
 * @author andres
 */
public class Area_DAO {



public static boolean insertarArea(Area area) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Insertando Area!");
            return false;
        }
        String query = "INSERT INTO area(nombre_sede, descripcion, estado_area)"
                + "VALUES ('"
                +  area.getNombreSede()
                + "','" + area.getDescripcion() + "', 'on')";
        System.out.println("\n\tQuery: " + query + "\n");
        System.out.println(query);
        try {
            s.executeUpdate(query);
             conexion.cerrarConexion();
            return true;
        } catch (SQLException se) {
            System.out.println("Error Insertando Area!");
             conexion.cerrarConexion();
            return false;
        }
    }

    public static List<Area> consultarArea() {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Consultando un Area!");
            return null;
        }
        String query = "SELECT nombre_sede,id_area,descripcion FROM area ORDER BY id_area";
        System.out.println("\n\tQuery: " + query + "\n");
        System.out.println(query);
        List<Area> listaAreas = new ArrayList<Area>();
        try {
            rs = s.executeQuery(query);


            /*
            Se recorre cada fila del ResultSet para instancia a cada empleado y agregarlo a la lista de empleados.
             */
            while (rs.next()) {
                /* Se instancia del nuevo Empleado. */
                Area tempArea = new Area(rs.getString("nombre_sede"), rs.getString("id_area"), rs.getString("descripcion"));
                /* Se agrega el empleado a la lista. */
                listaAreas.add(tempArea);
            }
        } catch (SQLException se) {
            se.printStackTrace();
            System.out.println("Error Consultado la area");
            return null;
        } finally {
            conexion.cerrarConexion();
        }
        //return listaSedes.toArray(new Sede[listaSedes.size()]);
        return listaAreas;
    }

    public JSONArray consultarAreaTabla(String query) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Consultando Areas!");
            return null;
        }
        System.out.println(query);
        try {
            rs = s.executeQuery(query);

            conexion.session.setHoldability(rs.HOLD_CURSORS_OVER_COMMIT);

            JSONArray areas = new JSONArray();
            try {
                int i = 0;
                while (rs.next()) {
                    JSONObject area = new JSONObject();
                    area.put("nombre_sede", rs.getString(1));
                    area.put("id_area", rs.getString(2));
                    area.put("descripcion", rs.getString(3));
                    area.put("estado_area", rs.getString(4));
                    area.put("tam", rs.getString(5));
                    areas.put(i, area);
                    i++;
                }
            } catch (Exception e) {
            }
            return areas;
        } catch (SQLException se) {
            se.printStackTrace();
            System.out.println("Error Consultado las areas");
            return null;
        }
    }

    public static boolean areaExiste(String descripcion, String nombreSede) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Estableciendo conexion Revisar sede! " + se.getMessage());
            return false;
        }

        String query = "SELECT * FROM area "
                + "WHERE descripcion = '" + descripcion + "' AND nombre_sede = '" + nombreSede + "';";

        System.out.println(query);
        try {
            rs = s.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException se) {
            se.printStackTrace();
            System.out.println("Error al consultar existencia de una Sede");
            return false;
        } finally {
            if (conexion != null) {
                conexion.cerrarConexion();
            }
        }
    }
    public static JSONObject getArea(String id_area) {
        ConexionBD conex = new ConexionBD();
        JSONObject areas = new JSONObject();

        try {
            Statement st = (Statement) conex.session.createStatement();

            ResultSet rs = st.executeQuery("select id_area,nombre_sede,descripcion from area where id_area = "+id_area);
            int i = 0;
            while (rs.next()) {
                JSONObject area = new JSONObject();
                area.put("id_area", rs.getString(1));                
                area.put("nombre_sede", rs.getString(2));
                area.put("descripcion", rs.getString(3));
                area.put("estado_area", rs.getString(4));
                areas.put("" + i++, area);
            }
        } catch (Exception e) {
            System.out.println("Error al consultar un area");
        }
        return areas;
    }

    public static boolean actualizarArea(Area area) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Actualizando Area!");
            return false;
        }
        String query = "update area set descripcion = '" + area.getDescripcion() + "', nombre_sede = '" + area.getNombreSede() +"' where id_area = "+area.getArea();
        System.out.println("\n\tQuery: " + query + "\n");
        System.out.println(query);
        try {
            s.executeUpdate(query);
            conexion.cerrarConexion();
            return true;
        } catch (SQLException se) {
            System.out.println("Error Actualizando Area!");
            conexion.cerrarConexion();
            return false;
        }
    }

     public static boolean desactivarArea(String idArea) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error desactivando Area!");
            return false;
        }
        String query = "UPDATE area set estado_area = 'off' where id_area = '" + idArea + "';";
        System.out.println(query);
        try {
            s.executeUpdate(query);
            return true;
        } catch (SQLException se) {
            se.printStackTrace();
            System.out.println("Error al desactivar area");
            return false;
        }
    }

  public static boolean activarArea(String idArea) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error desactivando Area!");
            return false;
        }
        String query = "UPDATE area set estado_area = 'on' where id_area = '" + idArea + "';";
        System.out.println(query);
        try {
            s.executeUpdate(query);
            return true;
        } catch (SQLException se) {
            se.printStackTrace();
            System.out.println("Error al desactivar area");
            return false;
        }
    }


}
